How IS NULL works in SQL? Best IS NULL examples

When working with SQL, dealing with NULL values is something you can’t escape. Understanding how IS NULL works is critical to writing effective queries and avoiding potential pitfalls.

What Does NULL Mean in SQL?

Before diving into IS NULL, let’s clarify what NULL actually represents in SQL. Unlike zero (0) or an empty string (‘ ‘), NULL signifies the absence of a value. This means:

It is not equivalent to zero or any other predefined value.
Arithmetic operations involving NULL result in NULL.
NULL does not equal any value, including itself.

Understanding IS NULL in SQL

The IS NULL operator is used to filter records where a column contains a NULL value. Since NULL does not behave like other values, the usual equality comparison (= NULL) will not work. Instead, SQL provides the IS NULL operator specifically for this purpose.

Basic Usage of IS NULL

Let’s look at a basic example of using IS NULL. Suppose we have a database table employees like this:

id	name	email
1	Alice	alice@example.com
2	Bob	NULL
3	Charlie	charlie@example.com

If we want to find employees who do not have an email assigned, we use IS NULL like this:

SELECT * FROM employees WHERE email IS NULL;

The result set will include:

id	name	email
2	Bob	NULL

Using IS NOT NULL

There are times when we need the opposite—fetching rows where the column has any value except NULL. This is achieved with IS NOT NULL:

SELECT * FROM employees WHERE email IS NOT NULL;

This will return:

id	name	email
1	Alice	alice@example.com
3	Charlie	charlie@example.com

NULL in Conditional Expressions

A common misconception is that NULL values can be checked using comparison operators like = or <>. However, in SQL:

SELECT * FROM employees WHERE email = NULL;

The above query will return zero results, even though there is a NULL value in the column. This happens because in SQL, NULL = NULL is not TRUE — it’s UNKNOWN. Instead, always use:

SELECT * FROM employees WHERE email IS NULL;

Handling NULL with COALESCE and IFNULL

Sometimes, you might want to replace NULL values with a default. For this, you can use functions like COALESCE or IFNULL.

COALESCE(column_name, replacement_value): Returns the first non-NULL value from the list.
IFNULL(column_name, replacement_value): MySQL-specific function that replaces NULL with the given value.

Example:

SELECT name, COALESCE(email, 'No Email') AS email_info FROM employees;

This will return:

name	email_info
Alice	alice@example.com
Bob	No Email
Charlie	charlie@example.com

Common Mistakes with IS NULL

Many developers run into issues when dealing with NULL. Here are some common mistakes and how to avoid them:

Using “column = NULL” instead of “IS NULL”
Always use IS NULL for checking NULL values.
Not accounting for NULL in calculations
Remember that any mathematical operation involving NULL results in NULL.
Forgetting to handle NULL in joins
NULL values can affect JOIN operations, leading to unexpected results.

Conclusion

Understanding how IS NULL works in SQL is crucial for writing correct queries, handling missing data, and avoiding logic errors. The key takeaways:

Use IS NULL to check for missing values.
Use IS NOT NULL when filtering out NULL values.
Never use = NULL, as it does not work as expected.
Use COALESCE or IFNULL to handle NULL values gracefully.

By mastering these concepts, you can confidently work with NULL values in SQL and avoid common mistakes.

Other interesting article:

How NULLIF works in SQL? Best NULLIF examples